{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>occur_time</th>\n",
       "      <th>serveID</th>\n",
       "      <th>metric_name</th>\n",
       "      <th>maximum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-02-11 15:26:10</td>\n",
       "      <td>1-230</td>\n",
       "      <td>CpuUsage</td>\n",
       "      <td>63.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-02-11 15:26:10</td>\n",
       "      <td>1-230</td>\n",
       "      <td>MemoryUsage</td>\n",
       "      <td>40.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-02-11 15:26:10</td>\n",
       "      <td>2-459</td>\n",
       "      <td>CpuUsage</td>\n",
       "      <td>63.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-02-11 15:26:10</td>\n",
       "      <td>2-459</td>\n",
       "      <td>MemoryUsage</td>\n",
       "      <td>40.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-02-11 15:26:20</td>\n",
       "      <td>1-230</td>\n",
       "      <td>CpuUsage</td>\n",
       "      <td>52.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2021-02-13 17:16:20</td>\n",
       "      <td>1-230</td>\n",
       "      <td>CpuUsage</td>\n",
       "      <td>95.32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           occur_time serveID  metric_name  maximum\n",
       "0 2021-02-11 15:26:10   1-230     CpuUsage    63.85\n",
       "1 2021-02-11 15:26:10   1-230  MemoryUsage    40.21\n",
       "2 2021-02-11 15:26:10   2-459     CpuUsage    63.85\n",
       "3 2021-02-11 15:26:10   2-459  MemoryUsage    40.21\n",
       "4 2021-02-11 15:26:20   1-230     CpuUsage    52.76\n",
       "5 2021-02-13 17:16:20   1-230     CpuUsage    95.32"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 读取数据集\n",
    "df1 = pd.read_excel('./实例文件.xlsx',sheet_name='data1')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>uid</th>\n",
       "      <th>occur_date</th>\n",
       "      <th>first_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>256</td>\n",
       "      <td>2020-12-30</td>\n",
       "      <td>2020-12-30 20:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256</td>\n",
       "      <td>2020-12-12</td>\n",
       "      <td>2020-12-12 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>256</td>\n",
       "      <td>2020-12-08</td>\n",
       "      <td>2020-12-08 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>256</td>\n",
       "      <td>2020-12-01</td>\n",
       "      <td>2020-12-01 21:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>256</td>\n",
       "      <td>2020-11-20</td>\n",
       "      <td>2020-11-20 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>258</td>\n",
       "      <td>2020-12-31</td>\n",
       "      <td>2020-12-31 23:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>258</td>\n",
       "      <td>2020-12-18</td>\n",
       "      <td>2020-12-18 19:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>258</td>\n",
       "      <td>2020-12-08</td>\n",
       "      <td>2020-12-08 02:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>258</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>2021-01-01 11:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>258</td>\n",
       "      <td>2020-10-20</td>\n",
       "      <td>2020-10-20 09:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   uid occur_date          first_time\n",
       "0  256 2020-12-30 2020-12-30 20:00:00\n",
       "1  256 2020-12-12 2020-12-12 09:00:00\n",
       "2  256 2020-12-08 2020-12-08 12:00:00\n",
       "3  256 2020-12-01 2020-12-01 21:00:00\n",
       "4  256 2020-11-20 2020-11-20 19:00:00\n",
       "5  258 2020-12-31 2020-12-31 23:00:00\n",
       "6  258 2020-12-18 2020-12-18 19:00:00\n",
       "7  258 2020-12-08 2020-12-08 02:00:00\n",
       "8  258 2021-01-01 2021-01-01 11:00:00\n",
       "9  258 2020-10-20 2020-10-20 09:00:00"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.read_excel('./实例文件.xlsx',sheet_name='data2')\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>serveID</th>\n",
       "      <th>maximum</th>\n",
       "      <th>minus_1/1000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1-230</td>\n",
       "      <td>63.85</td>\n",
       "      <td>63.78615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2-459</td>\n",
       "      <td>63.85</td>\n",
       "      <td>63.78615</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  serveID  maximum  minus_1/1000\n",
       "0   1-230    63.85      63.78615\n",
       "1   2-459    63.85      63.78615"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 场景一\n",
    "# 对所有服务器进行一次巡检，了解每台服务器在最近一周内 CPU 消耗最高使用情况，用于判断服务器资源是否足够支撑业务健康运行，为了过滤掉部分干扰，每台服务器需要去掉最高的千分之一数据\n",
    "today = '2021-02-13'\n",
    "begin_date = (pd.to_datetime(today) - datetime.timedelta(days=7)).strftime('%Y/%m/%d')\n",
    "end_date = (pd.to_datetime(today) - datetime.timedelta(days=1)).strftime('%Y/%m/%d')\n",
    "con1 = df1['occur_time'] >= begin_date\n",
    "con2 = df1['occur_time'] <= end_date\n",
    "df_cpu = df1[df1['metric_name'].str.contains('Cpu') & con1 & con2]\n",
    "df_group = df_cpu.groupby(['serveID'])['maximum'].max().reset_index()\n",
    "df_group['minus_1/1000'] = df_group['maximum'] * (1 - 0.001)\n",
    "df_group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda\\lib\\site-packages\\ipykernel_launcher.py:13: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  del sys.path[0]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>uid</th>\n",
       "      <th>时间段</th>\n",
       "      <th>首登次数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>256</td>\n",
       "      <td>上午</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>256</td>\n",
       "      <td>夜晚</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>258</td>\n",
       "      <td>夜晚</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   uid 时间段  首登次数\n",
       "1  256  上午     2\n",
       "3  256  夜晚     2\n",
       "7  258  夜晚     2"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 场景二\n",
    "# 分析玩家最后 4 个登录日每日首次上线时间段的偏好，便于某活动推广\n",
    "# 取每个玩家最后 4 条的登录流水，玩家历史登录流水不足 4 条的也取出\n",
    "# 取登录日首次上线时的小时，计算出每个玩家上线最多的时间段\n",
    "# [0点，6点) ——> 凌晨\n",
    "# [6点，12点) ——> 上午\n",
    "# [12点，18点) ——> 下午\n",
    "# [18点，24点) ——> 夜晚\n",
    "df2['hour'] = df2['first_time'].dt.hour\n",
    "df2_sort = df2.sort_values(by=['uid','occur_date'],ascending=True)\n",
    "df2_sort['组内降序排名'] = df2_sort.groupby(['uid'])['occur_date'].rank(method='dense',ascending=False).astype(int)\n",
    "df2_filiter = df2_sort[df2_sort['组内降序排名'] <= 4]\n",
    "df2_filiter['时间段'] = pd.cut(df2_filiter['hour'],[0,6,12,18,24],labels=['凌晨','上午','下午','夜晚'])\n",
    "df = df2_filiter.groupby(['uid','时间段'])['occur_date'].count().reset_index()\n",
    "df['组内降序排名'] = df.groupby(['uid'])['occur_date'].rank(method='dense',ascending=False).astype(int)\n",
    "df = df[df['组内降序排名'] <= 1]\n",
    "df = df.rename(columns={'occur_date':'首登次数'}).drop(columns=['组内降序排名'])\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
